﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL.PreAccept
{
    public class DamnEntity
    {
        public Int32 D_Id { get; set; }
        public string D_Name { get; set; }
        public string D_Relation { get; set; }
        public string D_Job { get; set; }
        public string D_SalaryPlace { get; set; }
        public string  D_Phone { get; set; }
        public string D_Moblie { get; set; }
        public string D_Address { get; set; }
        public string D_IdNum { get; set; }
        public Int32 R_Id { get; set; }
        public Int32 C_Id { get; set; }
    }

    public class Damn
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string ConnString = ConfigurationManager.ConnectionStrings["BankElKheir"].ToString();
        private static string CmdText = "";

        public static bool Add(DamnEntity s)
        {
            bool flag = true;

            CmdText = "Insert into Damn (D_Name ,D_Relation, D_Job,D_SalaryPlace,   D_Phone, D_Moblie, D_Address, D_IdNum, R_Id,C_Id) values" +
                                       "(@D_Name,@D_Relation,@D_Job,@D_SalaryPlace,@D_Phone,@D_Moblie,@D_Address,@D_IdNum,@R_Id,@C_Id)";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);


            cmd.Parameters.AddWithValue("@D_Name", s.D_Name);
            cmd.Parameters.AddWithValue("@D_Relation", s.D_Relation);
            cmd.Parameters.AddWithValue("@D_Job", s.D_Job);
            cmd.Parameters.AddWithValue("@D_SalaryPlace", s.D_SalaryPlace);
            cmd.Parameters.AddWithValue("@D_Phone", s.D_Phone);
            cmd.Parameters.AddWithValue("@D_Moblie", s.D_Moblie);
            cmd.Parameters.AddWithValue("@D_Address", s.D_Address);
            cmd.Parameters.AddWithValue("@D_IdNum", s.D_IdNum);
            cmd.Parameters.AddWithValue("@R_Id", s.R_Id);
            cmd.Parameters.AddWithValue("@C_Id", s.C_Id);

            try
            {
                Con.Open();
                int Num = cmd.ExecuteNonQuery();
                if (Num == 0)
                    flag = false;
            }

            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى ادخال البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;


        }

        public static bool Update(DamnEntity s)
        {
            bool flag = true;

            CmdText = "Update Damn set D_Name = @D_Name, D_Relation = @D_Relation , D_Job = @D_Job , D_SalaryPlace = @D_SalaryPlace , D_Phone = @D_Phone , D_Moblie  = @D_Moblie"+
                " , D_Address = @D_Address , D_IdNum = @D_IdNum , R_Id = @R_Id , C_Id = @C_Id where D_Id = @D_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@D_Name", s.D_Name);
            cmd.Parameters.AddWithValue("@D_Relation", s.D_Relation);
            cmd.Parameters.AddWithValue("@D_Job", s.D_Job);
            cmd.Parameters.AddWithValue("@D_SalaryPlace", s.D_SalaryPlace);
            cmd.Parameters.AddWithValue("@D_Phone", s.D_Phone);
            cmd.Parameters.AddWithValue("@D_Moblie", s.D_Moblie);
            cmd.Parameters.AddWithValue("@D_Address", s.D_Address);
            cmd.Parameters.AddWithValue("@D_IdNum", s.D_IdNum);
            cmd.Parameters.AddWithValue("@R_Id", s.R_Id);
            cmd.Parameters.AddWithValue("@C_Id", s.C_Id);
            cmd.Parameters.AddWithValue("@D_Id", s.D_Id);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تغيير البيانات " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;

        }

        public static bool Delete(Int32 s)
        {
            bool flag = true;

            CmdText = "Delete from Damn where D_Id = @D_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@D_Id", s);

            try
            {
                Con.Open();
                int num = cmd.ExecuteNonQuery();
                if (num == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ اثناء الحذف ", "رساله ادارية " + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                    flag = false;
                }
            }

            return flag;
        }

        public static List<DamnEntity> GetDamnByID(Int32 id)
        {
            DamnEntity x;
            List<DamnEntity> DamnList = new List<DamnEntity>();

            CmdText = "Select * from Damn where R_Id = @R_Id";

            SqlConnection Con = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@R_Id", id);

            try
            {
                Con.Open();

                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    DamnList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return DamnList;
        }



        public static List<DamnEntity> GetDamnByName(string Name)
        {
            {
                DamnEntity x;
                List<DamnEntity> DamnList = new List<DamnEntity>();

                CmdText = "Select * from Damn where D_Name = @D_Name";

                SqlConnection Con = new SqlConnection(ConnString);
                SqlCommand cmd = new SqlCommand(CmdText, Con);

                cmd.Parameters.AddWithValue("@D_Name", Name);

                try
                {
                    Con.Open();

                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        x = FillEntityFromReader(reader);
                        DamnList.Add(x);
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
                }
                finally
                {
                    if (Con.State == ConnectionState.Open)
                        Con.Close();
                }

                return DamnList;
            }
        }

        private static DamnEntity FillEntityFromReader(SqlDataReader reader)
        {

            DamnEntity x = new DamnEntity();
            x.R_Id = Convert.ToInt32(reader["R_Id"].ToString());
            if (reader["D_Id"] != null)
                x.D_Id = Int32.Parse(reader["D_Id"].ToString());
            if (reader["D_Name"] != null)
                x.D_Name = reader["D_Name"].ToString();
            if (reader["D_Relation"] != null)
                x.D_Relation = reader["D_Relation"].ToString();
            if ((reader["D_Job"] != null))
                x.D_Job = reader["D_Job"].ToString();
            if (reader["D_SalaryPlace"] != null)
                x.D_SalaryPlace = reader["D_SalaryPlace"].ToString();
            if (reader["D_Phone"] != null)
                x.D_Phone = reader["D_Phone"].ToString();
            if (reader["D_Moblie"] != null)
                x.D_Moblie = reader["D_Moblie"].ToString();
            if (reader["D_Address"] != null)
                x.D_Address = reader["D_Address"].ToString();
            if (reader["D_IdNum"] != null)
                x.D_IdNum = reader["D_IdNum"].ToString();
                

            return x;
        }
    }
}
